IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCOIUserIds]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCOIUserIds]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************            
* Name:   [GetCOIUserIds]         
* Purpose:  Retrieves the people directly under the passed in admPersonID 
			by joining the COI_Hierarchy_Map,ConflictOFInterest and AdmPerson tables.        
*            
* PARAMETERS            
* Name          Description                 
* -------------      -------------------------------------------            
* @domainUserId   Person Id (REQUIRED)              
* @firstName      FirstName of the user.            
* @lastName       LastName for the user.            
* @userName       UserName for the user.        
* @recursive      Determines whether to bring back the entire descendants or the direct descendants.        
* @sortColumn   Column to be arranged on.        
* @sortOrder   Determines the order(Ascending or Descending) to be sorted on the sort column          
*********************************************************************/            
CREATE PROCEDURE [dbo].[GetCOIUserIds]                
(                
@admPersonId INT,                
@firstName  VARCHAR(64)=null,                
@lastName VARCHAR(64)=null,                
@userName VARCHAR(36)=null,        
@recursive BIT = 1,        
@sortColumn VARCHAR(150)= 'LastName',        
@sortOrder BIT = 1, -->Asc 0-->Desc               
@HierarchyTypeId INT = 0  
)                
AS BEGIN                
                
	SET NOCOUNT ON                

	DECLARE @admPersonIds TABLE (SEQUENCE INT IDENTITY, [ID] INT,HierarchyTypeId INT)        
	      
	IF(@firstName IS NULL)
		SET @firstName = '%'                
	ELSE                 
		SET @firstName=@firstName+'%'                 
	                
	IF(@lastName IS NULL)                
		SET @lastName='%'                
	ELSE                
		SET @lastName=@lastName+'%'                 
	                
	IF(@userName IS NULL)                
		SET @userName='%'                
	ELSE                 
		SET @userName=@userName+'%'                 
	                
	IF @sortColumn='LastName' OR @sortColumn='UserName' OR @sortColumn='Role' OR @sortColumn='null'        
	BEGIN        
		INSERT INTO @admPersonIds
		SELECT adp.[Id],HierarchyTypeId FROM dbo.[GetDescendants](@admPersonId, @recursive,0,@HierarchyTypeId) des               
			INNER JOIN AdmPerson adp ON adp.[Id]=des.[ChildId]               
			LEFT OUTER JOIN  FWKDomainUSer fdu ON adp.[fwkDomainUserId]=fdu.[Id]        
			LEFT OUTER JOIN ConflictofInterest coi ON des.[ChildId] = coi.[admPersonId]       
				AND coi.[Id]= (SELECT MAX(Id) AS LatestCOIId FROM conflictofinterest WHERE admPersonId=adp.[Id])         
		WHERE                 
		ISNULL(adp.[firstName], '*') LIKE @firstName                
		AND                
		ISNULL(adp.[lastName], '*') LIKE @lastName                
		AND                
		ISNULL(fdu.[userName], '*') LIKE @userName             
		ORDER BY              
		CASE @sortOrder            
			WHEN 0 THEN             
				CASE @sortColumn            
					WHEN 'LastName' THEN adp.[LastName]        
					WHEN 'UserName' THEN fdu.[UserName]         
					WHEN 'Role' THEN adp.[COIRole]                                                     
					ELSE adp.[LastName]           
			END            
		END DESC,            
		CASE @sortOrder            
			WHEN 1 THEN             
				CASE @sortColumn            
					WHEN 'LastName' THEN adp.[LastName]        
					WHEN 'UserName' THEN fdu.[UserName]         
					WHEN 'Role' THEN adp.[COIRole]                                                     
					ELSE adp.[LastName]         
			END        
		END ,  
		CASE @sortOrder            
			WHEN 0 THEN             
				CASE @sortColumn            
					WHEN 'LastName' THEN adp.[FirstName]           
			END            
		END DESC,            
		CASE @sortOrder            
			WHEN 1 THEN             
				CASE @sortColumn            
					WHEN 'LastName' THEN adp.[FirstName]    
			END        
		END            
	END        
	ELSE IF @sortColumn='OutsideMember'        
	BEGIN        
		INSERT INTO @admPersonIds
		SELECT adp.[Id],HierarchyTypeId FROM dbo.[GetDescendants](@admPersonId, @recursive,0,@HierarchyTypeId) des                
			INNER JOIN AdmPerson adp ON adp.[Id] = des.[ChildId]               
			LEFT OUTER JOIN  FWKDomainUSer fdu ON adp.[fwkDomainUserId]=fdu.[Id]        
			LEFT OUTER JOIN ConflictofInterest coi ON des.[ChildId]= coi.[admPersonId]      
			AND coi.[Id]= (SELECT MAX(Id) AS LatestCOIId FROM conflictofinterest WHERE admPersonId=adp.[Id])        
		WHERE                 
		ISNULL(adp.[firstName], '*') like @firstName                
		AND                
		ISNULL(adp.[lastName], '*') like @lastName                
		AND                
		ISNULL(fdu.[userName], '*') like @userName             
		ORDER BY              
		CASE @sortOrder            
			WHEN 1 THEN             
				CASE @sortColumn            
					WHEN 'OutsideMember' THEN adp.[OutsideMember]                           
					ELSE adp.[OutsideMember]           
			END            
		END DESC,            
		CASE @sortOrder            
			WHEN 0 THEN             
				CASE @sortColumn            
					WHEN 'OutsideMember' THEN adp.[OutsideMember]                           
					ELSE adp.[OutsideMember]         
			END        
		END        
	END  

	SELECT DISTINCT Id,HierarchyTypeId FROM @admPersonIds

END 

--exec GetCOIUserIds 208960,null,null,null,1
